1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to a parallel optimizer for a relational database management system that allows the input of hints with a direct manipulation interface.
2. Description of Related Art.
Relational DataBase Management Systems (RDBMS) using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
The SQL interface allows users to formulate relational operations on one or more tables in the relational database, which in turn generate a result set that is a subset of data from the tables. While there have been various techniques developed for optimizing the performance of SQL statements, query optimization remains a difficult task.
Generally, an optimizer function within the RDBMS tries to find a query execution plan that will consume the least resources and run the fastest. Doing so requires information about the underlying tables in the relational database that might not necessarily be available.
To deal around the problem of insufficient information, an RDBMS typically provides the capability for the user to supply xe2x80x9chintsxe2x80x9d along with a query specification. These hints are usually specified as extra keywords or commands interspersed within the SQL statements using a xe2x80x9ccommand-syntaxxe2x80x9d approach. These hints provide the optimizer function of the RDBMS with help in the form of extra information that can be used to generate the most efficient query execution plan.
Optimizer hints influence the query execution plan. In the simplest terms, the optimizer of the RDBMS takes the query, plus any hints supplied, and generates a query execution plan that is executed by the RDBMS. Hints can be used to influence the optimizer to choose one plan over another when there is insufficient information for it to make the choice on its own.
The problem with the command-syntax approach is that the user must specify the hints using commands and keywords that indirectly affect the plan generated by the optimizer function. The optimizer interprets the hints along with the other information it knows about to generate the final query execution plan. There is no opportunity to directly affect the plan that is produced, and so several iterations of supplying additional or different hints may be necessary before the optimizer correctly xe2x80x9cguessesxe2x80x9d what the user has in mind. This results in a development cycle that is more difficult and time consuming than is necessary.
While there have been various techniques developed for optimizing the performance of RDBMS, there is a need in the art for techniques that provide improved user interfaces for adjusting query execution plans.
The present invention discloses a method, apparatus, and article of manufacture for directly manipulating a query for a relational database management system (RDBMS). The query is transformed into an operator tree that is displayed on a monitor, wherein the operator tree includes nodes for data sources and operators referenced in the query, and lines between the nodes. The RDBMS alters an execution plan for the query in response to one or more manipulations made to the displayed operator tree by the user. Generally, these manipulations comprise hints for an optimizer function of the RDBMS, so that an efficient execution plan can be generated for the query. Specifically, the hints influence the optimizer to choose one execution plan over another when there is insufficient information for the optimizer function to make a choice on its own.
An object of the present invention is to optimize the database access on parallel processing computer systems. Another object of the present invention is to improve the performance of database partitions managed by a parallel processing computer systems.